﻿USE [your_db_name]
GO

/****** Object:  Table [dbo].[ActivityTrackingLog] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/******************* user agent table **********************/
CREATE TABLE [dbo].[ActivityTrackingUserAgent](
	[Sequence] [int] IDENTITY(1,1) NOT NULL,
	[UserAgentId] [uniqueidentifier] NOT NULL,
	[ApplicationKey] [nvarchar](64) NULL,
	[Hash] [varchar](900) NULL,
	[AgentString] [nvarchar](MAX) NULL,
 CONSTRAINT [PK_ActivityTrackingUserAgent] PRIMARY KEY CLUSTERED 
(
	[UserAgentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
GO

CREATE NONCLUSTERED INDEX [idx_Hash] ON [dbo].[ActivityTrackingUserAgent] 
(
	[Hash] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO


/******************* log table **********************/
CREATE TABLE [dbo].[ActivityTrackingLog](
	[Sequence] [int] IDENTITY(1,1) NOT NULL,
	[ActivityLogId] [uniqueidentifier] NOT NULL,
	[ApplicationKey] [nvarchar](64) NULL,
	[ActivityType] [nvarchar](64) NOT NULL,
	[Category] [nvarchar](64) NULL,
	[ActivityCode] [nvarchar](15) NULL,
	[Activity] [nvarchar](64) NOT NULL,
	[IsPostAction] tinyint NOT NULL,
	[UserKey] [nvarchar](38) NULL,
	[ClientHost] [nvarchar](50) NULL,
	[PageUrl] [varchar](256) NULL,
	[QueryString] [varchar](1024) NULL,
	[UserAgentId] [uniqueidentifier] NULL,
	[TimeUtc] [datetime] NOT NULL,
 CONSTRAINT [PK_ActivityTrackingLog] PRIMARY KEY CLUSTERED 
(
	[ActivityLogId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
GO

ALTER TABLE [dbo].[ActivityTrackingLog]  WITH CHECK ADD FOREIGN KEY([UserAgentId])
REFERENCES [dbo].[ActivityTrackingUserAgent] ([UserAgentId])
ON UPDATE CASCADE
ON DELETE SET NULL

GO

CREATE NONCLUSTERED INDEX [idx_ApplicationKey] ON [dbo].[ActivityTrackingLog] 
(
	[ApplicationKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

CREATE NONCLUSTERED INDEX [idx_UserKey] ON [dbo].[ActivityTrackingLog] 
(
	[UserKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

